# -*- coding: utf-8 -*-

###########################################################################
#
#   Copyright (c) 2020  Bresee, Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License"
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
###########################################################################

"""
device table operate  设备表
add 
delete
modify
...
"""


import time
import utils.pgsql.base.pgconn as pgconn
import json

_tblName = "tbl_device"

_DB_SCRIPT = """
CREATE TABLE IF NOT EXISTS {} (
                "deviceId"  SERIAL PRIMARY KEY ,    
                "deviceGbCode" VARCHAR(64) NOT NULL default '' unique, --设备编码  
                "type" VARCHAR(128) NOT NULL default '', --设备类型 
                "caseId" INTEGER NOT NULL default 0,   -- 关联景区id   
                "deviceName"  VARCHAR(260) NOT NULL default '' ,   
                "streamProto" VARCHAR(12) NOT NULL default '' , -- 流协议 rtmp 
                "streamType" VARCHAR(260) NOT NULL default '' ,  -- 推流地址  
                "userName" VARCHAR(260) NOT NULL default '',  
                "pwd" VARCHAR(260) NOT NULL default '' ,    
                "description" VARCHAR(260) NOT NULL default '',    
                "deviceIp" VARCHAR(260) NOT NULL default '',    
                "states" VARCHAR(260) NOT NULL default '0',    
                "auditstatus" VARCHAR(260) NOT NULL default '未审核',    
                "detection" json,                                        
                "deviceAiboxId" INTEGER NOT NULL default 0,   
                "createTime" VARCHAR(32) NOT NULL default '',
                "updateTime" VARCHAR(32) NOT NULL default '',
                "vlogdata" json
                );

""".format(
    _tblName
)


class PgDevice:
    def __init__(self):
        self._pgConn = pgconn.pgMainConn

    def __del__(self):
        pass

    def createTbl(self):
        """
        create
        :return:
        """
        return self._pgConn.exec(_DB_SCRIPT)

     
    def addDevice(self, dic) -> str:
        dic["createTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        if "detection" in dic:
            dic["detection"] = json.dumps(dic["detection"])
        sql_key = '\"' + '\", \"'.join(dic.keys()) + '\"'
        sql_val = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_val = sql_val + '\'' + dic[key] + '\','
            else:
                sql_val = sql_val + str(dic[key]) + ' ,'

        
        sql = f"""insert into {_tblName} ({sql_key}) values ({sql_val[:-1]}) RETURNING "{"deviceId"}" """
        return pgconn.pgMainConn.exec_fetchone(sql)
        # sql_key = '\"' + '\", \"'.join(dic.keys()) + '\"'
        # sql_val = ''
        # for key in dic:
        #     if type(dic[key]) is str:
        #         sql_val = sql_val + '\'' + dic[key] + '\','
        #     else:
        #         sql_val = sql_val + str(dic[key]) + ' ,'

  
        # sql = "insert into {} ({}) values ({}) ".format(_tblName, sql_key, sql_val[:-1])
        # return pgconn.pgMainConn.exec(sql)

     
    def modify(self, ids, dic):
        dic["updateTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        if "detection" in dic:
            dic["detection"] = json.dumps(dic["detection"])
        sql_key = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_key = sql_key + '\"' + key + '\"' + ' = \'' + dic[key] + '\','
            else:
                sql_key = sql_key + '\"' + key + '\"' + ' = ' + str(dic[key]) + ' ,'


        sql =  "update {} set {} where  ".format(_tblName, sql_key[:-1])  + " \"deviceId\" = '{}' ".format(ids)
        print("modify",sql)
        return self._pgConn.exec(sql)

    
    def delete(self, ids) -> bool:
        sql = "delete from {} where \"deviceId\" = '{}' ".format(_tblName, ids)
        return self._pgConn.exec(sql)

     
    def get(self, ids) -> dict:
        sql = 'select * from tbl_device where "deviceId" = \'{}\' order by "deviceId" '.format(
            ids
        )
        return self._pgConn.exec_fetchone(sql)

    def get_deviceName(self, deviceName, caseId) -> dict:
        sql = "select * from {} where \"deviceName\" = '{}'  and  \"caseId\" = '{}'  ".format(
            _tblName, deviceName, caseId
        )
        return self._pgConn.exec_fetchone(sql)

    def get_deviceAiboxId(self,deviceIp) -> dict:
        # sql = "select * from {} where \"deviceAiboxId\" = \'{}\' and  \"caseId\" = \'{}\' ".format(_tblName, deviceAiboxId, caseId)
        # return self._pgConn.exec_fetchone(sql)
        sql = 'select a."deviceAiboxId" from {} a left Join tbl_case n on a."caseId"=n."caseId"  where n."dataValid" !=0 and a."deviceIp"=\'{}\'  order by "deviceAiboxId"'.format(
            _tblName,deviceIp
        )
        return self._pgConn.exec_fetchall(sql)

    def get_streamType(self, streamType) -> dict:
        sql = 'select a.* from {} a left Join tbl_case n on a."caseId"=n."caseId"  where n."dataValid" !=0 and "streamType" = \'{}\' '.format(
            _tblName, streamType
        )
        print(sql)
        return self._pgConn.exec_fetchall(sql)

    def allstreamType(self) -> list:
        sql = 'select a."streamType","deviceIp","deviceAiboxId" from tbl_device a left Join tbl_case n on a."caseId"=n."caseId"  where n."dataValid" !=0  '
        return self._pgConn.exec_fetchall(sql)

    def allByOperator_streamType(self, operatorId) -> list:
        sql = 'select a."streamType","deviceIp","deviceAiboxId" from tbl_device a left Join tbl_case n on a."caseId"=n."caseId"   and n."operatorId" = \'{}\' '.format(
            operatorId
        )
        return self._pgConn.exec_fetchall(sql)

    def allByCase_streamType(self, caseId) -> list:
        sql = 'select a."streamType","deviceIp","deviceAiboxId" from tbl_device a left Join tbl_case n on a."caseId"=n."caseId"   and a."caseId" = \'{}\' '.format(
            caseId
        )
        return self._pgConn.exec_fetchall(sql)

    def all(self) -> list:
        sql = 'select a.*,n."caseName" from {} a left Join tbl_case n on a."caseId"=n."caseId" where n."dataValid" !=0  order by a."deviceId" '.format(
            _tblName
        )
        return self._pgConn.exec_fetchall(sql)

    def allByOperator(self, operatorId) -> list:
        sql = 'select a.*,n."caseName" from tbl_device a left Join tbl_case n on a."caseId"=n."caseId"  where n."dataValid" !=0 and n."operatorId" = \'{}\' order by a."deviceId"'.format(
            operatorId, operatorId
        )
        return self._pgConn.exec_fetchall(sql)

     
    def allByCase(self, caseId, limit="") -> list:
        tmp = f' and a."deviceName" SIMILAR TO \'%({limit})%\' ' if limit else ""
        sql = 'select a.*,n."caseName" from {} a left Join tbl_case n on a."caseId"=n."caseId"  where n."dataValid" !=0 and a."caseId" = \'{}\' {} order by a."deviceId"'.format(
            _tblName, caseId, tmp
        )
        return self._pgConn.exec_fetchall(sql)
    
    def allinfoget(self) -> dict:
        sql = 'select * from tbl_device '
        return self._pgConn.exec_fetchall(sql)


    # 更新 设备在线状态
    def upda_status(self,streamType_str,dic) -> bool:
        dic["updateTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        sql_key = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_key = sql_key + '\"' + key + '\"' + ' = \'' + dic[key] + '\','
            else: # 值转化为字符串
                sql_key = sql_key + '\"' + key + '\"' + ' = ' + str(dic[key]) + ' ,'

        sql =  "update {} set {} where  ".format(_tblName, sql_key[:-1])+ " \"streamType\" = '{}' ".format(streamType_str)
        print("upda_status_sql:",sql)
        return self._pgConn.exec(sql)

    # 更新 groupld
    def upda_groupld(self,id_str,groupId) -> bool:

        sql =  "update {} set groupld ='{}' where  \"caseId\" = '{}' ".format(_tblName, groupId,id_str)
        print("upda_upda_groupld:",sql)
        return self._pgConn.exec(sql)


    # 获取景区下设备状态  AI
    def allstatesCase(self, caseId) -> list:
        sql = 'select "caseId","deviceGbCode","deviceId","AIstates" as "states","AIstatus" as "status" from {}  where "caseId" = \'{}\' order by "deviceId"'.format(
            _tblName, caseId, caseId
        )
        print(sql)
        return self._pgConn.exec_fetchall(sql)



    def update_auditstatus_State(self, auditstatus,states, deviceId) -> bool:
        sql = f"""update {_tblName} set "states" = '{states}',"auditstatus" = '{auditstatus}' where "deviceId" = '{deviceId}' """
        print(sql)
        return self._pgConn.exec(sql)



    # groupld
    def GBcode_select_groupld(self, GBcode) -> dict:
        sql = 'select "groupId","deviceId"  from {}  where "deviceGbCode" = \'{}\' '.format(
            _tblName, GBcode
        )
        print(sql)
        return self._pgConn.exec_fetchone(sql)



    def caseId_select_info(self, caseId) -> dict:
        sql = 'select "info"  from {}  where "caseId" = \'{}\' '.format(
            _tblName, caseId
        )
        print(sql)
        return self._pgConn.exec_fetchone(sql)


    def get_server_ip(self, hostname) -> dict:
        sql = 'SELECT "servername","ExternalIp" FROM "public"."servers" WHERE "serverType"=\'平台\' AND "groupId" =(SELECT "groupId" FROM "public"."servers" WHERE "ExternalIp"= \'{}\')'.format(
             hostname
        )
        print("get_server_ip",sql)
        return self._pgConn.exec_fetchone(sql)

    def get_record(self, casename) -> dict:
        sql = 'SELECT * FROM "public"."tbl_vlog_record" where "caseName" LIKE \'%{}%\' and "activityName" not LIKE  \'%{}%\''.format(casename,casename)
        print("get_record",sql)
        return self._pgConn.exec_fetchall(sql)

    def get_unnormal_device(self):
        sql = 'SELECT a.*,n."caseName" from tbl_device a left Join tbl_case n on a."caseId"=n."caseId" WHERE a."flvState"=FALSE ORDER BY "caseId","deviceId"'
        return self._pgConn.exec_fetchall(sql)